#coding=utf-8
from collections import OrderedDict
import random
from pyexcel_xls import get_data
from pyexcel_xls import save_data
import sys
import connect_mysql

#全局设置为UTF-8
reload(sys)
sys.setdefaultencoding('utf-8')

#引用connect_mysql程序连接数据库
connect_mysql.connect_mysql()

#小数点2位后全部截位函数
def jiewei(num):
    num_x, num_y = str(num).split('.')
    num = float(num_x + '.' + num_y[0:2])
    return num
#小数点后全部截位
def jiewei1(num):
    num_x, num_y = str(num).split('.')
    num = float(num_x + '.' + num_y[0:0])
    return int(num)

def read_xls_file():
    xls_data = get_data(r"C:\Users\Administrator\Desktop\01.xlsx")
    print "Get data type:", type(xls_data)
    for sheet_n in xls_data.keys():
        print sheet_n, ":", xls_data[sheet_n]
    for i in range(0,45,1):
        name = xls_data[sheet_n][i][0]
        originalprice = xls_data[sheet_n][i][1]
        rate_price = random.uniform(0.8, 0.9)
        rate_price = jiewei(rate_price)
        rate_price1 = str(rate_price * 10) + u'折'
        rate_chouqu = random.randint(5, 15)
        rate_chouqu1 = str(rate_chouqu) + '%'
        rate_lirun = random.randint(8, 12)
        rate_lirun1 = str(rate_lirun) + '%'
        price = jiewei1(originalprice * rate_price)
        price2 = int(originalprice) - price
        print name,u'原价为：'+ str(originalprice),u'折扣价为：' + str(price),u'折扣为：' + rate_price1,u'抽取率为：'+ rate_chouqu1,u'利润为：'+ rate_lirun1
        # 进行数据库写入
        connect = connect_mysql.connect_mysql()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # SQL 插入语句
        sql = "insert into mysql.goods(name,price, originalprice, discount, sellLabel_price, sellLabel_rate, rate_lirun) values('%s','%s','%s','%s','%s','%s','%s')" % (name,str(price), str(originalprice), rate_price1, price2,rate_chouqu1, rate_lirun1)
        # 执行sql语句
        input = cursor.execute(sql)
        connect.commit()  # 实例提交命令
        # cursor.close()
        # connect.close()
        print("insert ok")
        #return name,str(price),str(new_price),rate_price1,rate_chouqu1,rate_lirun1


# 写Excel数据, xls格式
def save_xls_file(i):
    i = i + 2
    data = OrderedDict()
    # sheet表的数据
    sheet_1 = []
    row_1_data = [u"商品名称", u"原价", u"折扣价", u"折扣", u"抽取率", u"利润率"]  # 每一行的数据
    row_i_data = [read_xls_file()[0], read_xls_file()[1], read_xls_file()[2],read_xls_file()[3],read_xls_file()[4],read_xls_file()[5]]
    # 逐条添加数据
    sheet_1.append(row_1_data)
    sheet_1.append(row_i_data)
    # 添加sheet表
    data.update({u"这是XX表": sheet_1})
    # 保存成xls文件
    save_data("C:\Users\Administrator\Desktop\write_test.xls", data)

if __name__ == '__main__':
    read_xls_file()
    #for i in range(0, 40, 1):
        #save_xls_file(i)





